package EBM.DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import EBM.model.User;

public class UserDAO {
	private List<Object> response;
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	private String sql;
	private int result;

	public UserDAO(){
		response = null;
		conn = null;
		ps = null;
		rs = null;
		sql = null;
		result = 0;
	}

	public List<Object> findAll() 
	throws SQLException, ClassNotFoundException{

		sql="select * from userinfo where type_usr < 2";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();		

		rs=ps.executeQuery();

		if(rs.next()){
			do{
				response.add(rs.getObject("usrid"));
				response.add(rs.getObject("name"));
			}while(rs.next());
		}
		conn.close();

		return response;
	}

	public List<Object> findByUserid(Object userId) 
	throws SQLException, ClassNotFoundException{

		sql="select * from userinfo where usrid=binary?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();		

		ps.setObject(1, userId);

		rs=ps.executeQuery();

		if(rs.next()){
			response.add(rs.getObject("usrid"));
		}
		conn.close();

		return response;
	}

	public User findByUseridNew(Object userId)
	throws SQLException, ClassNotFoundException{

		sql="select * from userinfo where usrid=binary?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();		

		ps.setObject(1, userId);

		rs=ps.executeQuery();
		User user = new User();
		if(rs.next()){
			user.setId(rs.getObject("id").toString());
			user.setName(rs.getObject("usrid").toString());
			user.setPwd(rs.getObject("pwd").toString());
			user.setName(rs.getObject("name").toString());
			user.setType_usr(rs.getObject("type_usr").toString());
		}
		conn.close();

		return user;
	}

	public List<Object> findByUseridAndPwd(Object userid, Object pwd) 
	throws SQLException, ClassNotFoundException{	

		String sql="select * from userinfo where usrid=binary? and pwd=binary?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		ps.setObject(1, userid);
		ps.setObject(2, pwd);

		response = new ArrayList<Object>();

		rs=ps.executeQuery();

		if(rs.next()){
			response.add(rs.getString("type_usr"));
		}
		conn.close();

		return response;
	}

	public List<Object> findByCertainProperty(Object proid, User user)
	throws SQLException, ClassNotFoundException{
		if (proid == null && user == null)
			return null;
		String sql="";

		if(proid.equals("-1")){
			sql = "select * from userinfo";
			List<String> conditions = new ArrayList<String>();
			if (user.getUsrid().equals("-1") == false)
				conditions.add(" userinfo.usrid='"+user.getUsrid()+"' ");
			if (user.getName().equals("-1") == false)
				conditions.add(" userinfo.name='"+user.getName()+"' ");
			if (user.getType_usr().equals("-1") == false)
				conditions.add(" userinfo.type_usr='"+user.getType_usr()+"' ");
			if(conditions.size() > 0)
			{
				sql += " where ";
				for (int i = 0; i < conditions.size(); i++)
				{
					sql += conditions.get(i);
					if (i < conditions.size() - 1)
						sql += "and";
				}
			}
		}
		else{
			sql = "select distinct userinfo.id,userinfo.usrid,userinfo.name,userinfo.type_usr"+
			" from userinfo,project_user,project"+
			" where userinfo.id = project_user.usrid"+
			" and project.id = project_user.proid"+
			" and project.proid='"+proid+"'" ;
			List<String> conditions = new ArrayList<String>();
			if (user.getUsrid().equals("-1") == false)
				conditions.add(" userinfo.usrid='"+user.getUsrid()+"' ");
			if (user.getName().equals("-1") == false)
				conditions.add(" userinfo.name='"+user.getName()+"' ");
			if (user.getType_usr().equals("-1") == false)
				conditions.add(" userinfo.type_usr='"+user.getType_usr()+"' ");
			if(conditions.size() > 0)
			{
				sql += " and ";
				for (int i = 0; i < conditions.size(); i++)
				{
					sql += conditions.get(i);
					if (i < conditions.size() - 1)
						sql += "and";
				}
			}
		}

		System.out.println("语句:"+sql);

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		rs = ps.executeQuery();
		response = new ArrayList<Object>();
		if(rs.next()){	
			do{
				User u = new User();
				u.setId(rs.getString("userinfo.id"));
				u.setUsrid(rs.getString("userinfo.usrid"));
				u.setName(rs.getString("userinfo.name"));
				u.setType_usr(rs.getString("userinfo.type_usr"));
				response.add(u);
			}while(rs.next());							
		}
		conn.close();	
		return response;
	}

	public boolean update(User user)
	throws SQLException, ClassNotFoundException{

		String sql="update userinfo set name=?,type_usr=? where usrid=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);			

		ps.setObject(1, user.getName());
		ps.setObject(2, user.getType_usr());
		ps.setObject(3, user.getUsrid());

		System.out.println(sql);

		result = ps.executeUpdate();

		response = new ArrayList<Object>();
		conn.close();

		if(result<1){
			return false;
		}else{
			return true;
		}
	}

	public boolean updatePwd(Object pwd, Object userid) 
	throws SQLException, ClassNotFoundException{

		String sql="update userinfo set pwd=? where usrid=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);			

		ps.setObject(1, pwd);
		ps.setObject(2, userid);

		result = ps.executeUpdate();
		response = new ArrayList<Object>();
		conn.close();

		if(result<1){
			return false;
		}else{
			return true;
		}
	}

	public boolean delete(User user) 
	throws SQLException, ClassNotFoundException{

		String sql="delete from userinfo where usrid=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);			

		ps.setObject(1, user.getUsrid());

		try{
			ps.executeUpdate();
		}catch(SQLException ex){
			return false;
		}finally{
			conn.close();
		}
		return true;
	}

	public boolean save(User user) 
	throws SQLException, ClassNotFoundException{

		String sql="insert into userinfo(usrid,name,type_usr) values(?,?,?)";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		response = new ArrayList<Object>();

		ps.setString(1, user.getUsrid());
		ps.setString(2, user.getName());
		ps.setString(3,user.getType_usr());

		try
		{
			result = ps.executeUpdate();
		}
		catch(Exception ex)
		{
			result = -1;
		}
		finally
		{
			conn.close();
		}

		if(result>0){
			return true;
		}else{
			return false;
		}
	}
}
